﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Zyg.Utility.DBUtility;
using Zyg.SSO.Core.Business;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class MySqlProvider : DALProvider
    {
        #region Sql Statements

        private static readonly string SqlSelectAccountAppUserById = "SELECT `ID`,`Name`,`Password`,`DateCreated`,`AccountAppID` FROM `AccountAppUser` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlSelectAccountAppUsersByAccountAppId = "SELECT `ID`,`Name`,`Password`,`DateCreated`,`AccountAppID` FROM `AccountAppUser` WHERE `AccountAppID` = @AccountAppId; set names utf8;";

        private static readonly string SqlInsertAccountAppUser = "INSERT INTO `AccountAppUser` (`ID`,`Name`,`Password`,`DateCreated`,`AccountAppID`) VALUES (@ID,@Name,@Password,@DateCreated,@AccountAppID); set names utf8;";

        private static readonly string SqlUpdateAccountAppUserById = "UPDATE `AccountAppUser` SET `Name` = @Name,`Password` = @Password,`DateCreated` = @DateCreated,`AccountAppID` = @AccountAppID WHERE `ID` = @ID; set names utf8;";

        private static readonly string SqlDeleteAccountAppUserById = "DELETE FROM `AccountAppUser` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlDeleteAccountAppUsersByAccountAppId = "DELETE FROM `AccountAppUser` WHERE `AccountAppID` = @AccountAppID; set names utf8;";

        #endregion

        public override Zyg.SSO.Core.Business.AccountAppUser SelectAccountAppUser(Guid id)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, id);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountAppUserById);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.AccountAppUser aau = new AccountAppUser();

                if (!reader.IsDBNull(1)) aau.Name = reader.GetString(1);
                if (!reader.IsDBNull(2)) aau.Password = reader.GetString(2);
                if (!reader.IsDBNull(3)) aau.DateCreated = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) aau.AccountAppID = reader.GetGuid(4);

                reader.Close();

                return aau;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertAccountAppUser(Zyg.SSO.Core.Business.AccountAppUser accoutAppUser)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accoutAppUser.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, accoutAppUser.Name);
            db.AddParameter("@Password", MySqlDbType.VarChar, accoutAppUser.Password);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, accoutAppUser.DateCreated);
            db.AddParameter("@AccountAppID", MySqlDbType.VarChar, accoutAppUser.AccountAppID);

            db.ExecuteSql(SqlInsertAccountAppUser);
        }

        public override void DeleteAccountAppUser(Zyg.SSO.Core.Business.AccountAppUser accoutAppUser)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accoutAppUser.Id);

            db.ExecuteSql(SqlDeleteAccountAppUserById);
        }

        public override void DeleteAccountAppUsers(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@AccountAppID", MySqlDbType.VarChar, accountApp.Id);

            db.ExecuteSql(SqlDeleteAccountAppUsersByAccountAppId);
        }

        public override void UpdateAccountAppUser(Zyg.SSO.Core.Business.AccountAppUser accoutAppUser)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accoutAppUser.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, accoutAppUser.Name);
            db.AddParameter("@Password", MySqlDbType.VarChar, accoutAppUser.Password);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, accoutAppUser.DateCreated);
            db.AddParameter("@AccountAppID", MySqlDbType.VarChar, accoutAppUser.AccountAppID);

            db.ExecuteSql(SqlUpdateAccountAppUserById);
        }

        public override IList<Zyg.SSO.Core.Business.AccountAppUser> FillAccountAppUsers(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            MySqlUtility db = new MySqlUtility();

            IList<AccountAppUser> aaus = new List<AccountAppUser>();

            MySqlDataReader reader;

            string accountAppId = accountApp.Id.ToString();

            db.AddParameter("@AccountAppId", MySqlDbType.VarChar, accountAppId);

            reader = db.ExecuteSqlReader(SqlSelectAccountAppUsersByAccountAppId);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Business.AccountAppUser aau = new AccountAppUser();

                    if (!reader.IsDBNull(0)) aau.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) aau.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) aau.Password = reader.GetString(2);
                    if (!reader.IsDBNull(3)) aau.DateCreated = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) aau.AccountAppID = reader.GetGuid(4);

                    aau.MarkOld();

                    aaus.Add(aau);
                }

                reader.Close();
            }

            return aaus;
        }

    }
}
